﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Text.RegularExpressions;

namespace TravelexForwardContracts
{
    class XAllFECExcel
    {
        private String m_filepath;
        private DateTime m_date;

        /***************************************************************************
         *  Public Interface 
         * *************************************************************************/
        public DataSetAllFEC processExcel(String in_file, DateTime in_datetime)
        {
            m_filepath = in_file;
            m_date = in_datetime;

            //Step1 : retrieve data from All FEC
            using (DataSet dsOri = readAllFEC())
            {
                using (DataSet dsValid = buildValidDataSet(dsOri))
                {
                    using (DataSet dsNoEmpty = deleteEpmtyRows(dsValid))
                    {
                        if (dsNoEmpty == null)
                        {
                            HMsg("[NG]processExcel", "");
                            return null;
                        }
                        DataSetAllFEC dsAllFEC = new DataSetAllFEC();
                        foreach (DataRow tmpRow in dsNoEmpty.Tables[0].Rows)
                        {
                            DataRow row = dsAllFEC.Tables[0].NewRow();
                            row["Counterparty"] = tmpRow["Counterparty"];
                            row["ClientName"] = tmpRow["Counterparty Client Name"];
                            row["Number"] = tmpRow["Number"];
                            row["OpeningDate"] = tmpRow["Opening Date"];
                            row["ValueDate"] = tmpRow["Value Date"];
                            row["Direction"] = tmpRow["Direction"];
                            row["CCY"] = tmpRow["CCY"];
                            row["AmountLeft"] = tmpRow["Amount Left"];
                            row["DealtRate"] = tmpRow["Dealt Rate"];
                            row["CCY2"] = tmpRow["CCY 2"];
                            row["BaseAmountLeft"] = tmpRow["Base Amount Left"];
                            row["CurrentSpotRate"] = tmpRow["Current Spot Rate"];
                            row["ForwardRate"] = tmpRow["Forward Rate"];
                            row["Ccy2Position"] = tmpRow["ccy 2 position"];
                            row["BaseCcy"] = tmpRow["Base ccy"];
                            row["BaseAmount"] = tmpRow["Base Amount"];
                            row["PercentOutOfTheMoney"] = tmpRow["Percent Out of The Money"];
                            row["UpdateDate"] = tmpRow["UpdateDate"];

                            dsAllFEC.Tables[0].Rows.Add(row);
                        }
                        dsAllFEC.AcceptChanges();

                        return dsAllFEC;
                    }
                }
            }
        }

        /*****************************************************************************
         *  Private Functions 
         * ***************************************************************************/
        private DataSet readAllFEC()
        {
            String sqlFindAll = "SELECT * FROM [Sheet1$]";
            String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_filepath +
                ";Extended Properties='Excel 8.0;IMEX=1';";
            try
            {
                using (OleDbDataAdapter oleAdapter = new OleDbDataAdapter(sqlFindAll, strConn))
                {
                    DataSet dsOriginal = new DataSet();
                    oleAdapter.Fill(dsOriginal, "[Sheet1$]");
                    return dsOriginal;
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("readPaymentExcel Error :" + ex.Message);
            }
        }

        private DataSet deleteEpmtyRows(DataSet in_ds)
        {
            try
            {
                DataSet tmpDataSet = new DataSet();
                tmpDataSet = in_ds.Copy();
                List<int> emptyRowIndex = new List<int>();
                int colIndex = 1;

                foreach (DataRow row in tmpDataSet.Tables[0].Rows)
                {
                    bool isRowsEmpty = false;
                    foreach (DataColumn col in tmpDataSet.Tables[0].Columns)
                    {
                        if (row[col].ToString() != "")
                        {
                            isRowsEmpty = false;
                            break;
                        }
                        else
                            isRowsEmpty = true;
                    }
                    if (isRowsEmpty)
                    {
                        emptyRowIndex.Add(colIndex);
                        row.Delete();
                    }
                    colIndex++;
                }
                tmpDataSet.Tables[0].AcceptChanges();

                return tmpDataSet;
            }
            catch (System.Exception ex)
            {
                HMsg("[NG]deleteEmptyRows",ex.Message);
                return null;
            }
        }

        private DataSet buildValidDataSet(DataSet in_ds)
        {
            try
            {
                DataSet tmpDataSet = new DataSet();
                tmpDataSet = in_ds.Copy();
                tmpDataSet.Tables[0].Columns.Remove("F1");
                tmpDataSet.Tables[0].Columns.Add("UpdateDate", typeof(string));

                //Delete Counterparty and CCY2(Currency)
                foreach (DataRow row in tmpDataSet.Tables[0].Rows)
                {
                    string strCounterparty = row["Counterparty"].ToString().Trim();
                    Match matchCounterparty = Regex.Match((string)strCounterparty,@"^Counterparty",RegexOptions.IgnoreCase);
                    Match matchCCY2 = Regex.Match((string)strCounterparty, @"^CCY", RegexOptions.IgnoreCase);

                    if(matchCounterparty.Success || matchCCY2.Success)
                    {
                        row.Delete();
                    }
                }
                tmpDataSet.Tables[0].AcceptChanges();       
                return tmpDataSet;
            }
            catch (System.Exception ex)
            {
                HMsg("[NG] buildValidPaymentDataSet",ex.Message);
                return null;
            }

        }

        private void HMsg(String msg, String ex)
        {
            using (HMessageBox xMsg = new HMessageBox())
            {
                xMsg.logMsg(msg, ex);
            }
        }
    }
}
